﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DbAdapter.sqldbAdapter;
using System.Data.SqlClient;
using System.Data;

namespace DAO.WaiGaoQiao
{
    /// <summary>
    /// 零星物品申领
    /// </summary>
    public class DataAccessTbtSporadicApp
    {
        /// <summary>
        /// 保存零星物品申领
        /// </summary>
        /// <param name="igpara"></param>
        /// <returns></returns>
        public int SaveSundries(object[] igpara)
        {
            sqldb db = new sqldb();
            return db.ExecuteNonQuery(GetConntionString.conntionstring(), "Proc_TbtSporadicApp", igpara);
        }

        /// <summary>
        /// 获取一条零星物品申领信息
        /// </summary>
        /// <param name="ID">主键</param>
        /// <returns>零星物品申领信息</returns>
        public DataRow GetInstockGroup(int ID)
        {
            string query = "select [ID] ,[AppDt],[SpType],[Goods_ID],[Count],[UserMan],[Remark],[RecordDateTime],[OperatorID],[OrderNo] "
                     + "from [Tbt_SporadicApp] where ID = " + ID.ToString();
            sqldb db = new sqldb();
            return db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query).Tables[0].Rows[0];
        }


        /// <summary>
        /// 获取零星物品申领信息(分页）
        /// </summary>
        /// <param name="pagesize">页大小</param>
        /// <param name="startindex">起始页</param>
        /// <param name="dict">查询条件</param>
        /// <returns>零星物品申领信息</returns>
        public DataSet GetInstockGroup(int pagesize, int startindex,
            string condition)
        {
            int startrow = pagesize * (startindex - 1);
            int endrow = pagesize * startindex + 1;
            StringBuilder str = new StringBuilder();
            str.Append("select[ID] ,[AppDt],[SpType],[Goods_ID],[Count],[UserMan],[Remark],[RecordDateTime],[OperatorID],[OrderNo] from (");
            str.Append("select ROW_NUMBER() OVER(ORDER BY ID DESC) rownumber,[ID] ,[AppDt],[SpType],[Goods_ID],[Count],[UserMan],[Remark],[RecordDateTime],[OperatorID],[OrderNo]");
            str.Append("from [Tbt_SporadicApp] where 1=1 ");
            str.Append(condition);
            str.Append(") a ");
            str.Append("where a.rownumber > ");
            str.Append(startrow);
            str.Append(" and a.rownumber < ");
            str.Append(endrow);
            sqldb db = new sqldb();
            return db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, str.ToString());
        }

        /// <summary>
        /// 获取零星物品申领信息(不分页）
        /// </summary>
        /// <param name="dict">查询条件</param>
        /// <returns>零星物品申领信息</returns>
        public DataSet GetInstockGroup(string condition)
        {
            StringBuilder str = new StringBuilder();
            str.Append("select [ID] ,[AppDt],[SpType],[Goods_ID],[Count],[UserMan],[Remark],[RecordDateTime],[OperatorID],[OrderNo] from ");
            str.Append("[Tbt_SporadicApp] where 1=1 ");
            str.Append(condition);
            sqldb db = new sqldb();
            return db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, str.ToString());
        }

        /// <summary>
        /// 删除零星物品申领
        /// </summary>
        /// <param name="ID">主键</param>
        /// <returns></returns>
        public int DeleteInstockGroup(string id)
        {
            int rowcount = 0;
            using (SqlConnection sqlconn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqlconn.Open();
                SqlTransaction sqltran = sqlconn.BeginTransaction();
                try
                {
                    sqldb db = new sqldb();
                    StringBuilder str = new StringBuilder();
                    str.Append("delete from [Tbt_SporadicApp] where  "+ id);
                    rowcount = db.ExecuteNonQuery(sqltran, CommandType.Text, str.ToString());
                    sqltran.Commit();
                    sqlconn.Close();
                }
                catch (Exception ex)
                {
                    sqltran.Rollback();
                    sqlconn.Close();
                    throw new Exception(ex.Message);
                }
            };
            return rowcount;
        }

        /// <summary>
        /// 获取零星物品的数据
        /// </summary>
        /// <returns></returns>
        public int GetRowCount(string condition)
        {
            string query = "select count(1) from [Tbt_SporadicApp] Where 1=1 " + condition + "";
            sqldb db = new sqldb();
            object rowcount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return Convert.ToInt32(rowcount);
        }
        /// <summary>
        /// 获取批号
        /// </summary>
        /// <returns></returns>
        public string GetInstockSporadicOrderNo()
        {
            sqldb db = new sqldb();
            string query = "Execute GetCon_Tbt_SporadicApp";
            object strorderno = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return strorderno.ToString();
        }
    }
}
